﻿using Dapper;
using ERPEF.Model;
using ERPIService;
using Microsoft.Data.SqlClient;
using Microsoft.EntityFrameworkCore;
using S4Project;
using S4Project.Model;
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Security.Cryptography;
using System.Text;
using System.Threading.Tasks;

namespace ERPService
{
    public class QuanXianService : QuanXianIService
    {
        private readonly MyDbContext MyDbContext;
        private readonly QuanXianIService quanXianIService;
        public QuanXianService(MyDbContext myDbcontext)
        {
            this.MyDbContext = myDbcontext;
           
        }

        public async Task<List<Roles>> GetAllRoles()
        {
            return await MyDbContext.Roles.ToListAsync();
        }

        public async Task<List<User>> Query()
        {
            return await MyDbContext.User.ToListAsync();
        }

        public async Task<bool> CreateUserWithRolesAsync(User user, List<int> roleIds)
        {
            // 添加用户
            MyDbContext.User.Add(user);
            await MyDbContext.SaveChangesAsync();

            // 遍历角色ID列表并分配角色
            foreach (var roleId in roleIds)
            {
                UserRoles userRole = new UserRoles
                {
                    UserID = (int)user.UserID,
                    RolesID = roleId
                };
                MyDbContext.UserRoles.Add(userRole);
            }

            try
            {
                await MyDbContext.SaveChangesAsync();
                return true;
            }
            catch
            {
                return false;
            }
        }

        public async Task<bool> UpdateUserRolesAsync(int userId, List<int> roleIds)
        {
            // 首先删除当前用户的所有角色关联
            var userRoles = await MyDbContext.UserRoles
                .Where(ur => ur.UserID == userId)
                .ToListAsync();
            MyDbContext.UserRoles.RemoveRange(userRoles);
            await MyDbContext.SaveChangesAsync();

            // 然后添加新的角色关联
            foreach (var roleId in roleIds)
            {
                UserRoles userRole = new UserRoles
                {
                    UserID = userId,
                    RolesID = roleId
                };
                MyDbContext.UserRoles.Add(userRole);
            }

            try
            {
                await MyDbContext.SaveChangesAsync();
                return true;
            }
            catch
            {
                return false;
            }
        }

        public async Task<bool> DeleteUserWithRolesAsync(int userId)
        {
            // 获取用户的角色关联信息
            var userRoles = await MyDbContext.UserRoles
                .Where(ur => ur.UserID == userId)
                .ToListAsync();

            // 删除用户的角色关联
            MyDbContext.UserRoles.RemoveRange(userRoles);

            // 删除用户
            var user = await MyDbContext.User.FindAsync(userId);
            if (user == null) return false;
            MyDbContext.User.Remove(user);

            try
            {
                await MyDbContext.SaveChangesAsync();
                return true;
            }
            catch
            {
                return false;
            }
        }








        public async Task<List<rolesuser>> UserSelects()
        {
            using (var con = new SqlConnection(MyDbContext.conStr))
            {
                string sql = "SELECT ur.*, r.* FROM UserRoles ur INNER JOIN Roles r ON ur.RolesID = r.RolesID;";
                
                try
                {
                    await con.OpenAsync(); // 打开连接也是异步的  
                    var Rolo = await con.QueryAsync<rolesuser>(sql); // 等待查询完成  
                    return Rolo.ToList();
                }
                catch (Exception ex)
                {
                   
                    throw;
                }
            }
        }

        public async Task<List<Trees>> TreesSelects()
        {
            using (SqlConnection connection = new SqlConnection(MyDbContext.conStr))
            {
                string sql = "select [JurilD],[JurName],[JurPid]\r\nfrom [dbo].[Jurisdiction]";
                List<Jurisdiction> quans = (List<Jurisdiction>)await connection.QueryAsync<Jurisdiction>(sql);

                List<Trees> trees = GetChild(quans, 0);
                return trees;
            }
        }

        private List<Trees> GetChild(List<Jurisdiction> quans, int v)
        {
            List<Trees> trees = new List<Trees>();
            List<Jurisdiction> Pquans = quans.Where(e => e.JurPid == v).ToList();
            foreach (Jurisdiction item in Pquans)
            {
                Trees trees1 = new Trees()
                {
                    Id = item.JurilD,
                    authName = item.JurName
                };
                trees1.children = GetChild(quans, item.JurilD);
                trees.Add(trees1);
            }
            return trees;
        }





        public async Task<int[]> GetQid(int rid)
        {
            using (SqlConnection connection = new SqlConnection("Data Source=rm-cn-em93outyl0004m9o.sqlserver.rds.aliyuncs.com, 1433;Initial Catalog=ERP_Project;User ID=k123_123h;Password=Qw123456;Encrypt=False;"))
            {
                string sql = $@"select [JuriID]  from [dbo].[RolesJurisdiction] where [RolesID]='{rid}'";
                return connection.Query<int>(sql).ToArray();
            }
        }

        public async Task<bool> Addquan(int[] c, int pid)
        {
            using (SqlConnection connection = new SqlConnection("Data Source=rm-cn-em93outyl0004m9o.sqlserver.rds.aliyuncs.com, 1433;Initial Catalog=ERP_Project;User ID=k123_123h;Password=Qw123456;Encrypt=False;"))
            {
                string selectSql = $@"select * from [dbo].[RolesJurisdiction] where [RolesID]='{pid}'";
                var res = connection.Query<int>(selectSql).ToArray();

                if (res != null && res.Length > 0)
                {
                    string deleteSql = $@"DELETE FROM RolesJurisdiction WHERE RolesID='{pid}'";
                    using (SqlCommand deleteCommand = new SqlCommand(deleteSql, connection))
                    {
                        await connection.OpenAsync();
                        int rowsAffected = await deleteCommand.ExecuteNonQueryAsync(); // 异步执行删除操作
                        if (rowsAffected > 0)
                        {
                            foreach (var b in c)
                            {
                                string insertSql = $@"INSERT INTO RolesJurisdiction (RolesID, JuriID) VALUES ('{pid}','{b}')";
                                using (SqlCommand insertCommand = new SqlCommand(insertSql, connection))
                                {
                                    await insertCommand.ExecuteNonQueryAsync(); // 异步执行插入操作
                                }
                            }
                        }
                        await connection.CloseAsync();
                    }
                    return true;
                }
                else
                {
                    foreach (var b in c)
                    {
                        string insertSql = $@"INSERT INTO RolesJurisdiction (RolesID, JuriID) VALUES ('{pid}','{b}')";
                        using (SqlCommand insertCommand = new SqlCommand(insertSql, connection))
                        {
                            await connection.OpenAsync();
                            await insertCommand.ExecuteNonQueryAsync(); // 异步执行插入操作
                            await connection.CloseAsync();
                        }
                    }
                    return true;
                }
            }
        }

        public Task<Roles> rbac_Roles()
        {
            var maxIdRole = MyDbContext.Roles.OrderByDescending(r => r.RolesID).FirstOrDefault();
            return Task.FromResult(maxIdRole);
        }

        public async Task<bool> addroid(Roles roles)
        {
            try
            {
                await MyDbContext.Roles.AddAsync(roles);
                await MyDbContext.SaveChangesAsync(); // 保存更改到数据库
                return true; // 添加成功
            }
            catch (Exception ex)
            {
                // 处理异常情况
                // 可以记录日志或者返回相应的错误信息
                return false; // 添加失败
            }
        }

        public async Task<bool> deleterblo(int roid)
        {
            var deleteid = MyDbContext.Roles.FirstOrDefault(p => p.RolesID == roid);
            if (deleteid != null)
            {
                var c = MyDbContext.Roles.Remove(deleteid);
                MyDbContext.SaveChanges();
                if (c != null)
                {
                    using (SqlConnection connection = new SqlConnection("Data Source=rm-cn-em93outyl0004m9o.sqlserver.rds.aliyuncs.com, 1433;Initial Catalog=ERP_Project;User ID=k123_123h;Password=Qw123456;Encrypt=False;"))
                    {
                        string deleteSql = $@"DELETE FROM RolesJurisdiction WHERE RolesID='{roid}'";
                        using (SqlCommand deleteCommand = new SqlCommand(deleteSql, connection))
                        {
                            await connection.OpenAsync();
                            int rowsAffected = await deleteCommand.ExecuteNonQueryAsync(); // 异步执行删除操作
                            return true;
                        }
                        return true;
                    }
                    return true;
                }
                return false;
            }
            return false;
        }

        public async Task<List<CeBianlan>> CeBianlanSelectAsync(int JuriID)
        {
            using (SqlConnection connection = new SqlConnection(MyDbContext.conStr))
            {
                string sql = $@"select  qx.JurilD,JurName,JurAddress,JurPid,RolesID
                            from RolesJurisdiction as jsqx
                            inner join Jurisdiction as qx
                            on qx.JurilD=jsqx.JuriID
                            where RolesID={JuriID} and JurPid=0
                            order by qx.JurilD asc";
                List<CeBianlan> list = (List<CeBianlan>)await connection.QueryAsync<CeBianlan>(sql);
                foreach (CeBianlan item in list)
                {
                    string sql1 = $@"select  qx.JurilD,JurName,JurAddress,JurPid,RolesID
                            from RolesJurisdiction as jsqx
                            inner join Jurisdiction as qx
                            on qx.JurilD=jsqx.JuriID
                            where RolesID={JuriID} and JurPid={item.JurilD}
                            order by qx.JurilD asc";
                    item.ulist = (List<CeBianlan>)await connection.QueryAsync<CeBianlan>(sql1);
                }
                return list;
            }
        }

        public async Task<UserRoles> queryU_rolesiD(int RolesID)
        {
            return await MyDbContext.UserRoles
 .FirstOrDefaultAsync(ur => ur.UserID == RolesID);
        }
    }
}
